<?php
/**
 * Class 处理数据
 * @package app\common\command
 * Description:
 * User: sunnier
 * Email: xiaoyao_xiao@126.com
 * Date: 2019-08-07
 * Time: 13:00
 */
namespace app\common\command;
use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\Output;
class Import  extends Command
{
	protected function configure()
	{
		$this->setName('import_data')
			->addArgument('filename',Argument::OPTIONAL,'文件路径')
            ->addArgument('type',Argument::OPTIONAL,'文件类型')
			->setDescription('导入数据到数据库');
	}

	/**
	 * @param Input $input
	 * @param Output $output
	 * @return int|void|null
	 */
	protected function execute(Input $input, Output $output)
	{
		$fileName =$input->getArgument('filename');
        $type =$input->getArgument('type');
        try {
            if(empty($type)) {
                $inputFileType = 'Xlsx';
            }else{
                $inputFileType = $type;
            }
            $inputFileName = app()->getRootPath() . '/public/'.$fileName.'.xlsx';
            $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
            /**  Load $inputFileName to a Spreadsheet Object  **/
            $worksheetData = $reader->listWorksheetInfo($inputFileName);
            $PHPExcel = $reader->load($inputFileName);
            $provinces = cache('area_provinces');
            if (empty($provinces)) {
                $provinces = model('common/Area')->where('pid', 0)
                    ->column('title', 'id');
                foreach ($provinces as $k => $v) {
                    $provinces[$k] = [
                        $v, str_replace(['省', '市'], '', $v)
                    ];
                }
                cache('area_provinces', $provinces);
            }
            $blackModel = model('common/BlackList');
            $commentModel = model('common/Comment');
            $nowTime = time();
            foreach ($worksheetData as $k => $sheetValArr) {
                $sheetVal = $sheetValArr['worksheetName'];
                $provinceId = 0;
                $provinceName = '';
                foreach ($provinces as $pId => $pv) {
                    $flag = false;
                    foreach ($pv as $pvv) {
                        if (strpos($sheetVal, $pvv) !== false) {
                            $provinceId = $pId;
                            $flag = true;
                            break;
                        }
                    }
                    if ($flag) {
                        break;
                    }
                }
                if(empty($provinceId)){
                    continue;
                }
                $cities = [];
                if (!empty($provinceId)) {
                    $provinceName = $provinces[$provinceId][0];
                    $cities = cache('area_cities_' . $provinceId);
                    if (empty($cities)) {
                        $cities = model('common/Area')->where('pid', $provinceId)
                            ->column('title', 'id');
                        foreach ($cities as $ck => $cv) {
                            $cities[$ck] = [
                                $cv, str_replace('市', '', $cv)
                            ];
                        }
                        cache('area_cities_' . $provinceId, $cities);
                    }
                }
                $sheet = $PHPExcel->getSheet($k); // 读取第一個工作表
                $highestRow = $sheet->getHighestRow(); // 取得总行数
                $highestColumm = $sheet->getHighestColumn(); // 取得总列数
                if (strlen($highestColumm) > 1) {
                    $highestColumm = 'Z';
                }
                $data = [];
                for ($row = 3; $row <= $highestRow; $row++) //行号从3开始
                {
                    for ($column = 'A'; $column <= $highestColumm; $column++) //列数是以A列开始
                    {
                        $cellVal=$sheet->getCell($column . $row)->getValue();
                        if (!empty($cellVal)) {
                            if (is_string($cellVal)) {
                                $data[$row][$column] = $cellVal;
                            } else {
                                if(is_object($cellVal)){
                                    $data[$row][$column] = $cellVal->__toString();
                                }else{
                                    $data[$row][$column] = strval($cellVal);
                                }
                            }
                        }
                    }
                }
                foreach ($data as $dk => $dv) {
                    //先插入公司数据
                    $title = filterEmoji($dv['A']);
                    unset($dv['A']);
                    $cityName = '';
                    $cityId = 0;
                    foreach ($cities as $cId => $cv) {
                        $flag = false;
                        foreach ($cv as $cvv) {
                            if (strpos($title, $cvv) !== false) {
                                $cityId = $cId;
                                $cityName = $cv[0];
                                $flag = true;
                                break;
                            }
                        }
                        if ($flag) {
                            break;
                        }
                    }
                    $description = $provinceName . '的黑名单企业';
                    if(!empty($cityName)) {
                        $name = $cityName . '的黑名单企业';
                    }else{
                        $name='';
                    }
                    $blackData = [
                        'type' => 1,
                        'province_id' => $provinceId,
                        'city_id' => $cityId,
                        'title' => $title,
                        'description' => $description,
                        'name' => $name,
                        'create_time' => $nowTime,
                        'update_time' => $nowTime
                    ];
                    $blackId = $blackModel->insertGetId($blackData);
                    $commentData = [];
                    foreach ($dv as $ddk => $ddv) {
                        $commentData[] = [
                            'black_id' => $blackId,
                            'content' => filterEmoji(strval($ddv)),
                            'create_time' => $nowTime,
                            'update_time' => $nowTime
                        ];
                    }
                    if(!empty($commentData)) {
                        $commentModel->insertAll($commentData);
                    }
                }
                $data = [];
                $output->writeln(date('Y-m-d H:i:s')."执行插入'.$sheetVal.'的数据完成！");
            }
            $output->writeln(date('Y-m-d H:i:s')."执行插入数据完成！");
            exit();
        }catch (\Exception $e){
            $output->writeln(date('Y-m-d H:i:s')."执行插入数据出错！".$e->getTraceAsString());
            exit();
        }
	}
}